
package com.epam.android.training.level1.db.dao.impl;

import com.epam.android.training.level1.db.dao.PollDao;
import com.epam.android.training.level1.db.poll.Poll.Answer;
import com.epam.android.training.level1.db.poll.Poll.PollSuite;
import com.epam.android.training.level1.db.poll.Poll.Question;
import com.epam.android.training.level1.db.poll.PollDbHelper;
import com.epam.android.training.level1.poll.AnswerBo;
import com.epam.android.training.level1.poll.PollBo;
import com.epam.android.training.level1.poll.QuestionBo;
import com.epam.android.training.level1.util.IOUtils;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteStatement;
import android.provider.BaseColumns;

import java.util.ArrayList;
import java.util.List;

/**
 * @author Anton Novikau
 */
public class PollDaoImpl implements PollDao {

  private static final String[] ID_PROJECTION = {
    BaseColumns._ID
  };

  private static final String[] FULL_POLL_PROJECTION = {
      PollSuite._ID, PollSuite.DISPLAY_NAME, PollSuite.PASSED_PERCENT
  };

  private static final String[] FULL_QUESTION_PROJECTION = {
      Question._ID, Question.POLL_ID, Question.DISPLAY_NAME
  };

  private static final String[] FULL_ANSWER_PROJECTION = {
      Answer._ID, Answer.QUESTION_ID, Answer.DISPLAY_NAME, Answer.IS_CORRECT
  };

  private static final String INSERT_QUESTION_STMT = "INSERT INTO " + Question.TABLE_NAME + "("
      + Question.POLL_ID + "," + Question.DISPLAY_NAME + ")" + " VALUES(?,?)";

  private static final String INSERT_ANSWER_STMT = "INSERT INTO " + Answer.TABLE_NAME + "("
      + Answer.QUESTION_ID + "," + Answer.DISPLAY_NAME + "," + Answer.IS_CORRECT + ")"
      + " VALUES(?,?,?)";

  private final PollDbHelper dbHelper;

  public PollDaoImpl(Context context) {
    this.dbHelper = new PollDbHelper(context);
  }

  @Override
  public String getNewestPollTitle() {
    SQLiteDatabase db = dbHelper.getReadableDatabase();
    Cursor pollCursor = null;
    String newestPollTitle = "";
    try {
      pollCursor = db.query(PollSuite.TABLE_NAME, FULL_POLL_PROJECTION, null, null, null, null,
          null);
      if (pollCursor.moveToLast()) {
        newestPollTitle = pollCursor.getString(pollCursor.getColumnIndex(PollSuite.DISPLAY_NAME));
      }
    } finally {
      IOUtils.closeCursor(pollCursor);
      db.close();
    }
    return newestPollTitle;
  }

  @Override
  public List<Long> getAllPollIds() {
    List<Long> pollIds = new ArrayList<Long>();
    SQLiteDatabase db = dbHelper.getReadableDatabase();
    Cursor pollCursor = null;
    try {
      pollCursor = db.query(PollSuite.TABLE_NAME, ID_PROJECTION, null, null, null, null, null);
      // iterate over cursor with step 1.
      while (pollCursor.move(1)) {
        pollIds.add(pollCursor.getLong(pollCursor.getColumnIndex(PollSuite._ID)));
      }
    } finally {
      IOUtils.closeCursor(pollCursor);
      db.close();
    }
    return pollIds;
  }

  @Override
  public List<PollBo> findAllPolls() {
    List<PollBo> polls = new ArrayList<PollBo>();
    SQLiteDatabase db = dbHelper.getReadableDatabase();
    Cursor pollCursor = null;
    try {
      pollCursor = db.query(PollSuite.TABLE_NAME, FULL_POLL_PROJECTION, null, null, null, null,
          null);

      while (pollCursor.move(1)) {
        PollBo poll = new PollBo();
        long pollId = pollCursor.getLong(pollCursor.getColumnIndex(PollSuite._ID));
        poll.setId(pollId);
        poll.setDisplayName(pollCursor.getString(pollCursor.getColumnIndex(PollSuite.DISPLAY_NAME)));
        poll.setPassedPercent(pollCursor.getInt(pollCursor.getColumnIndex(PollSuite.PASSED_PERCENT)));

        poll.setQuestions(findQuestionIds(pollId));
        polls.add(poll);
      }
    } finally {
      IOUtils.closeCursor(pollCursor);
      db.close();
    }
    return polls;
  }

  @Override
  public PollBo findPoll(long pollId) {
    PollBo poll = null;
    SQLiteDatabase db = dbHelper.getReadableDatabase();
    Cursor pollCursor = null;
    try {
      pollCursor = db.query(PollSuite.TABLE_NAME, FULL_POLL_PROJECTION,
          buildStrictSelection(PollSuite._ID), new String[] {
            String.valueOf(pollId)
          }, null, null, null);

      if (pollCursor.moveToFirst()) {
        poll = new PollBo();
        poll.setId(pollCursor.getLong(pollCursor.getColumnIndex(PollSuite._ID)));
        poll.setDisplayName(pollCursor.getString(pollCursor.getColumnIndex(PollSuite.DISPLAY_NAME)));
        poll.setPassedPercent(pollCursor.getInt(pollCursor.getColumnIndex(PollSuite.PASSED_PERCENT)));

        poll.setQuestions(findQuestionIds(pollId));
      }
    } finally {
      IOUtils.closeCursor(pollCursor);
      db.close();
    }
    return poll;
  }

  @Override
  public QuestionBo findQuestion(long questionId) {
    QuestionBo question = null;
    SQLiteDatabase db = dbHelper.getReadableDatabase();
    Cursor questionCursor = null;
    try {
      questionCursor = db.query(Question.TABLE_NAME, FULL_QUESTION_PROJECTION,
          buildStrictSelection(Question._ID), new String[] {
            String.valueOf(questionId)
          }, null, null, null);

      if (questionCursor.moveToFirst()) {
        question = new QuestionBo();
        question.setId(questionCursor.getLong(questionCursor.getColumnIndex(Question._ID)));
        question.setPollId(questionCursor.getLong(questionCursor.getColumnIndex(Question.POLL_ID)));
        question.setDisplayName(questionCursor.getString(questionCursor
            .getColumnIndex(Question.DISPLAY_NAME)));
        question.setAnswers(findAnswers(questionId));
      }
    } finally {
      IOUtils.closeCursor(questionCursor);
      db.close();
    }
    return question;
  }

  @Override
  public List<QuestionBo> findQuestions(long pollId) {
    List<QuestionBo> questions = new ArrayList<QuestionBo>();
    SQLiteDatabase db = dbHelper.getReadableDatabase();
    Cursor questionCursor = null;
    try {
      questionCursor = db.query(Question.TABLE_NAME, FULL_QUESTION_PROJECTION,
          buildStrictSelection(Question.POLL_ID), new String[] {
            String.valueOf(pollId)
          }, null, null, null);

      while (questionCursor.move(1)) {
        QuestionBo question = new QuestionBo();
        questions.add(question);
        long questionId = questionCursor.getLong(questionCursor.getColumnIndex(Question._ID));
        question.setId(questionId);
        question.setPollId(questionCursor.getLong(questionCursor.getColumnIndex(Question.POLL_ID)));
        question.setDisplayName(questionCursor.getString(questionCursor
            .getColumnIndex(Question.DISPLAY_NAME)));
        question.setAnswers(findAnswers(questionId));
      }
    } finally {
      IOUtils.closeCursor(questionCursor);
      db.close();
    }
    return questions;
  }

  @Override
  public long createPoll(String pollName, List<QuestionBo> questions) {
    SQLiteDatabase db = dbHelper.getWritableDatabase();
    SQLiteStatement insertQuestion = null;
    SQLiteStatement insertAnswer = null;
    try {
      db.beginTransaction();

      ContentValues values = new ContentValues();
      values.put(PollSuite.DISPLAY_NAME, pollName);
      long pollId = db.insert(PollSuite.TABLE_NAME, PollSuite.DISPLAY_NAME, values);

      insertQuestion = db.compileStatement(INSERT_QUESTION_STMT);
      insertAnswer = db.compileStatement(INSERT_ANSWER_STMT);

      for (QuestionBo question : questions) {
        insertQuestion.clearBindings();
        insertQuestion.bindLong(1, pollId);
        insertQuestion.bindString(2, question.getDisplayName());

        long questionId = insertQuestion.executeInsert();
        question.setId(questionId);
        question.setPollId(pollId);

        List<AnswerBo> answers = question.getAnswers();
        for (AnswerBo answer : answers) {
          insertAnswer.clearBindings();
          insertAnswer.bindLong(1, questionId);
          insertAnswer.bindString(2, answer.getDisplayName());
          insertAnswer.bindLong(3, answer.isCorrect() ? 1 : 0);

          long answerId = insertAnswer.executeInsert();
          answer.setId(answerId);
          answer.setQuestionId(questionId);
        }
      }

      db.setTransactionSuccessful();

      return pollId;
    } finally {
      if (db.inTransaction()) {
        db.endTransaction();
      }
      IOUtils.closeStmt(insertAnswer);
      IOUtils.closeStmt(insertQuestion);
      db.close();
    }
  }

  @Override
  public void savePollResult(long pollId, int result) {
    SQLiteDatabase db = dbHelper.getWritableDatabase();

    try {
      ContentValues resultData = new ContentValues();
      resultData.put(PollSuite.PASSED_PERCENT, result);
      db.update(PollSuite.TABLE_NAME, resultData, buildStrictSelection(PollSuite._ID),
          new String[] {
            String.valueOf(pollId)
          });
    } finally {
      db.close();
    }
  }

  private List<Long> findQuestionIds(long pollId) {
    List<Long> questionIds = new ArrayList<Long>();
    SQLiteDatabase db = dbHelper.getReadableDatabase();
    Cursor questionsCursor = null;
    try {
      questionsCursor = db.query(Question.TABLE_NAME, ID_PROJECTION,
          buildStrictSelection(Question.POLL_ID), new String[] {
            String.valueOf(pollId)
          }, null, null, null);

      while (questionsCursor.move(1)) {
        questionIds.add(questionsCursor.getLong(questionsCursor.getColumnIndex(Question._ID)));
      }
    } finally {
      IOUtils.closeCursor(questionsCursor);
      db.close();
    }
    return questionIds;
  }

  private List<AnswerBo> findAnswers(long questionId) {
    List<AnswerBo> answers = new ArrayList<AnswerBo>();
    SQLiteDatabase db = dbHelper.getReadableDatabase();
    Cursor answerCursor = null;
    try {
      answerCursor = db.query(Answer.TABLE_NAME, FULL_ANSWER_PROJECTION,
          buildStrictSelection(Answer.QUESTION_ID), new String[] {
            String.valueOf(questionId)
          }, null, null, null);

      while (answerCursor.move(1)) {
        AnswerBo answer = new AnswerBo();
        answer.setId(answerCursor.getLong(answerCursor.getColumnIndex(Answer._ID)));
        answer.setQuestionId(answerCursor.getLong(answerCursor.getColumnIndex(Answer.QUESTION_ID)));
        answer.setDisplayName(answerCursor.getString(answerCursor
            .getColumnIndex(Answer.DISPLAY_NAME)));
        answer.setCorrect(answerCursor.getInt(answerCursor.getColumnIndex(Answer.IS_CORRECT)));
        answers.add(answer);
      }
    } finally {
      IOUtils.closeCursor(answerCursor);
      db.close();
    }
    return answers;
  }

  private String buildStrictSelection(String... columns) {
    StringBuilder builder = new StringBuilder();
    boolean firstTime = true;
    for (String column : columns) {
      if (firstTime) {
        firstTime = false;
      } else {
        builder.append(" AND ");
      }
      builder.append(column).append(" = ?");
    }
    return builder.toString();
  }
}
